﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using GovernmentOfficeProject.LevelDataAccessModel;
using System.Data;
using System.Data.SqlClient;
using GovernmentOfficeProject.SysTools;
using System.Text;


namespace GovernmentOfficeProject.LevelDataAccess
{
    public class DAL_USER_Department
    {

        public DAL_USER_Department()
        {

        }

        /// <summary>
        /// 根据部门ID,获取指定部门.
        /// </summary>
        /// <param name="deptID">部门ID</param>
        /// <returns></returns>
        public Entity_USER_Department getDeptByDeptID(decimal deptID)
        {           
            try
            {
                string strSQL = "select * from [PER_DEPARTMENT] where DeptID = @deptID";
                SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@deptID",deptID)
                };
                DataSet dst = DAL_DBGM.GetDataSet(strSQL,values);
                Entity_USER_Department department = new Entity_USER_Department();
                foreach (DataRow row in dst.Tables[0].Rows)
                {                    
                    department.DeptID = Convert.ToDecimal(row["DeptID"]);
                    department.DeptName = Convert.ToString(row["DeptName"]);
                    department.DeptDesc = Convert.ToString(row["DeptDesc"]);
                    department.DeptParentID = Convert.ToDecimal(row["DeptParentID"]);
                    department.CreateUserID = Convert.ToDecimal(row["DeptCreateUserID"]);
                    department.CreateDateTime = Convert.ToString(row["DeptCreateDate"]);
                    department.ModifyUserID = Convert.ToDecimal(row["DeptModifyUserID"]);
                    department.ModifyDateTime = Convert.ToString(row["DeptModifyDate"]);
                    department.RecordStatus = Convert.ToDecimal(row["DeptRecordStatus"]);
                }
                return department;
            }
            catch (Exception ex)
            {
                AppLog.Write("---异常位置:LevelDataAccess.DAL_USER_Department.GetDeptByDeptID(decimal deptID)---", AppLog.LogMessageType.Error, ex);
                throw ex;
            }
        }

        /// <summary>
        /// 根据用户ID,获取用户对应的所有部门.
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns></returns>
        public List<Entity_USER_Department> getDeptListByUserID(decimal userID)
        {
            try
            {
                //string strSQL = "select * from [PER_DEPARTMENT] where DeptID = @deptID";
                string strSQL = "select B.* from [PER_USER] A,[PER_DEPARTMENT] B,[PER_USER_DEPARTMENT] M WHERE A.UserID = M.UDUserID AND B.DeptID = M.UDDEPTID	AND M.UDUserID = @userID";
                SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@userID",userID)
                };
                DataSet dst = DAL_DBGM.GetDataSet(strSQL, values);
                List<Entity_USER_Department> deptList = new List<Entity_USER_Department>();
                Entity_USER_Department department = null;
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    department = new Entity_USER_Department();
                    department.DeptID = Convert.ToDecimal(row["DeptID"]);
                    department.DeptName = Convert.ToString(row["DeptName"]);
                    department.DeptDesc = Convert.ToString(row["DeptDesc"]);
                    department.DeptParentID = Convert.ToDecimal(row["DeptParentID"]);
                    department.CreateUserID = Convert.ToDecimal(row["DeptCreateUserID"]);
                    department.CreateDateTime = Convert.ToString(row["DeptCreateDate"]);
                    department.ModifyUserID = Convert.ToDecimal(row["DeptModifyUserID"]);
                    department.ModifyDateTime = Convert.ToString(row["DeptModifyDate"]);
                    department.RecordStatus = Convert.ToDecimal(row["DeptRecordStatus"]);
                    deptList.Add(department);
                }
                return deptList;
            }
            catch (Exception ex)
            {
                AppLog.Write("---异常位置:LevelDataAccess.DAL_USER_Department.GetDeptListByUserID(decimal userID)---", AppLog.LogMessageType.Error, ex);
                throw ex;
            }
        }

        /// <summary>
        /// 获取所有部门信息.
        /// </summary>
        /// <returns></returns>
        public List<Entity_USER_Department> getDeptList()
        {
            try
            {
                //string strSQL = "select * from [PER_DEPARTMENT] where DeptID = @deptID";
                string strSQL = "select * from [PER_DEPARTMENT]";
                DataSet dst = DAL_DBGM.GetDataSet(strSQL);
                List<Entity_USER_Department> deptList = new List<Entity_USER_Department>();
                Entity_USER_Department department = null;
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    department = new Entity_USER_Department();
                    department.DeptID = Convert.ToDecimal(row["DeptID"]);
                    department.DeptName = Convert.ToString(row["DeptName"]);
                    department.DeptDesc = Convert.ToString(row["DeptDesc"]);
                    department.DeptParentID = Convert.ToDecimal(row["DeptParentID"]);
                    department.CreateUserID = Convert.ToDecimal(row["DeptCreateUserID"]);
                    department.CreateDateTime = Convert.ToString(row["DeptCreateDate"]);
                    department.ModifyUserID = Convert.ToDecimal(row["DeptModifyUserID"]);
                    department.ModifyDateTime = Convert.ToString(row["DeptModifyDate"]);
                    department.RecordStatus = Convert.ToDecimal(row["DeptRecordStatus"]);
                    deptList.Add(department);
                }
                return deptList;
            }
            catch (Exception ex)
            {
                AppLog.Write("---异常位置:LevelDataAccess.DAL_USER_Department.getDeptartmentList()---", AppLog.LogMessageType.Error, ex);
                throw ex;
            }
        }


        /// <summary>
        /// 根据传入实体类对象的参数,进行多条件查找,并返回部门列表.
        /// </summary>
        /// <param name="deptEntity">用于参数传递的实体类</param>
        /// <returns>部门列表</returns>
        public List<Entity_USER_Department> getDeptListByDeptEntity(Entity_USER_Department deptEntity)
        {
            try
            {
                string strSQL = "select * from [PER_DEPARTMENT] where 1=1";
                if (deptEntity.DeptName != "" && deptEntity.DeptName != null)
                {
                    strSQL += " and DeptName='" + deptEntity.DeptName + "'";
                }
                if (deptEntity.DeptDesc != "" && deptEntity.DeptDesc != null)
                {
                    strSQL += " and DeptDesc='" +deptEntity.DeptDesc +"'";
                }
                if (deptEntity.DeptParentID != -1)
                {
                    strSQL += " and DeptParentID = " + deptEntity.DeptParentID;
                }
                if (deptEntity.CreateUserID != -1)
                {
                    strSQL += " and DeptCreateUserID = " + deptEntity.CreateUserID;
                }
                if (deptEntity.CreateDateTime != "" && deptEntity.CreateDateTime != null)
                {
                    strSQL += " and DeptCreateDate = '" +deptEntity.CreateDateTime+"'";
                }
                if (deptEntity.ModifyUserID != -1)
                {
                    strSQL += " and DeptModifyUserID = " + deptEntity.ModifyUserID;
                }
                if (deptEntity.ModifyDateTime != "" && deptEntity.ModifyDateTime != null)
                {
                    strSQL += " and DeptModifyDate = '" + deptEntity.ModifyDateTime + "'";
                }
                if (deptEntity.RecordStatus != -1)
                {
                    strSQL += " and DeptRecordStatus = " + deptEntity.RecordStatus;
                }
                //AppLog.Write("getDeptListByDeptEntity-SQL:" + strSQL, AppLog.LogMessageType.Debug);
                DataSet dst = DAL_DBGM.GetDataSet(strSQL);
                List<Entity_USER_Department> deptList = new List<Entity_USER_Department>();
                Entity_USER_Department department = null;
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    department = new Entity_USER_Department();
                    department.DeptID = Convert.ToDecimal(row["DeptID"]);
                    department.DeptName = Convert.ToString(row["DeptName"]);
                    department.DeptDesc = Convert.ToString(row["DeptDesc"]);
                    department.DeptParentID = Convert.ToDecimal(row["DeptParentID"]);
                    department.CreateUserID = Convert.ToDecimal(row["DeptCreateUserID"]);
                    department.CreateDateTime = Convert.ToString(row["DeptCreateDate"]);
                    department.ModifyUserID = Convert.ToDecimal(row["DeptModifyUserID"]);
                    department.ModifyDateTime = Convert.ToString(row["DeptModifyDate"]);
                    department.RecordStatus = Convert.ToDecimal(row["DeptRecordStatus"]);
                    deptList.Add(department);
                }
                return deptList;
            }
            catch (Exception ex)
            {
                AppLog.Write("---异常位置:LevelDataAccess.DAL_USER_Department.getDeptListByDeptEntity(Entity_USER_Department deptEntity)---", AppLog.LogMessageType.Error, ex);
                throw ex;
            }
        }


        /// <summary>
        /// 添加一个部门.
        /// </summary>
        /// <param name="deptEntity">实体类,用户传递参数</param>
        /// <returns>true:添加部门成功;false:添加部门失败.</returns>
        public bool addDeptartment(Entity_USER_Department deptEntity)
        {
            try
            {
                bool flag = false;
                bool blState = false;
                StringBuilder sqlBudIns = new StringBuilder();
                StringBuilder sqlBudValue = new StringBuilder();
                sqlBudIns.Append("insert into [PER_DEPARTMENT](");

                sqlBudValue.Append(" values(");
                List<SqlParameter> sprList = new List<SqlParameter>();
                if (deptEntity.DeptName != "" && deptEntity.DeptName != null)
                {
                    sqlBudIns.Append("DeptName,");
                    sqlBudValue.Append("@deptName,");
                    sprList.Add(new SqlParameter("@deptName", deptEntity.DeptName));
                    blState = true;
                }
                if (deptEntity.DeptDesc != "" && deptEntity.DeptDesc != null)
                {
                    sqlBudIns.Append("DeptDesc,");
                    sqlBudValue.Append("@deptDesc,");
                    sprList.Add(new SqlParameter("@deptDesc", deptEntity.DeptDesc));
                    blState = true;
                }
                if (deptEntity.DeptParentID != -1)
                {
                    sqlBudIns.Append("DeptParentID,");
                    sqlBudValue.Append("@deptParentID,");
                    sprList.Add(new SqlParameter("@deptParentID", deptEntity.DeptParentID));
                    blState = true;
                }
                if (deptEntity.CreateUserID != -1)
                {
                    sqlBudIns.Append("DeptCreateUserID,");
                    sqlBudValue.Append("@deptCreateUserID,");
                    sprList.Add(new SqlParameter("@deptCreateUserID", deptEntity.CreateUserID));
                    blState = true;
                }
                if (deptEntity.CreateDateTime != "" && deptEntity.CreateDateTime != null)
                {
                    sqlBudIns.Append("DeptCreateDate,");
                    sqlBudValue.Append("@deptCreateDate,");
                    sprList.Add(new SqlParameter("@deptCreateDate", deptEntity.CreateDateTime));
                    blState = true;
                }
                if (deptEntity.ModifyUserID != -1)
                {
                    sqlBudIns.Append("DeptModifyUserID,");
                    sqlBudValue.Append("@deptModifyUserID,");
                    sprList.Add(new SqlParameter("@deptModifyUserID", deptEntity.ModifyUserID));
                    blState = true;
                }
                if (deptEntity.ModifyDateTime != "" && deptEntity.ModifyDateTime != null)
                {
                    sqlBudIns.Append("DeptmodifyDate,");
                    sqlBudValue.Append("@deptModifyDate,");
                    sprList.Add(new SqlParameter("@deptModifyDate", deptEntity.ModifyDateTime));
                    blState = true;
                }
                if (deptEntity.RecordStatus != -1)
                {
                    sqlBudIns.Append("DeptRecordstatus,");
                    sqlBudValue.Append("@deptRecordStatus,");
                    sprList.Add(new SqlParameter("@deptRecordStatus", deptEntity.RecordStatus));
                    blState = true;
                }

                if (blState)
                {
                    string strSQL = sqlBudIns.ToString().Substring(0, sqlBudIns.Length - 1) + ")" + sqlBudValue.ToString().Substring(0, sqlBudValue.Length - 1) + ")";
                    SqlParameter[] values = sprList.ToArray();
                    AppLog.Write("DAL-增加部门的SQL语句: " + strSQL, AppLog.LogMessageType.Debug);
                    flag = DAL_DBGM.InsertInfo(strSQL, values);
                    AppLog.Write("DAL-增加部门的SQL语句: " + flag, AppLog.LogMessageType.Debug);
                }
                else
                {
                    //添加用户失败:没有任何数据.
                    AppLog.Write("添加用户失败:没有任何数据.", AppLog.LogMessageType.Info);
                }

                return flag;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---异常位置:LevelDataAccess.DAL_USER_Department.addDeptartment(Entity_USER_Department deptEntity)---", AppLog.LogMessageType.Error, ex);
                throw ex;
            }
        }


        /// <summary>
        /// 根据部门ID,修改指定部门资料.
        /// </summary>
        /// <param name="deptID">待修改的部门ID</param>
        /// <param name="deptEntity">待修改的部门资料</param>
        /// <returns>true:修改部门成功;false:修改部门失败.</returns>
        public bool updateDeptByDeptID(decimal deptID, Entity_USER_Department deptEntity)
        {
            try
            {
                bool flag = false;
                bool blstate = false;
                string strSQL = "update [PER_DEPARTMENT] set";
                if (deptEntity.DeptName != "" && deptEntity.DeptName != null)
                {
                    strSQL += " DeptName='" + deptEntity.DeptName + "',";
                    blstate = true;
                }
                if (deptEntity.DeptDesc != "" && deptEntity.DeptDesc != null)
                {
                    strSQL += " DeptDesc='" + deptEntity.DeptDesc + "',";
                    blstate = true;
                }
                if (deptEntity.DeptParentID != -1)
                {
                    strSQL += " DeptParentID = " + deptEntity.DeptParentID + ","; 
                    blstate = true;
                }
                if (deptEntity.CreateUserID != -1)
                {
                    strSQL += " DeptCreateUserID = " + deptEntity.CreateUserID;
                    blstate = true;
                }
                if (deptEntity.CreateDateTime != "" && deptEntity.CreateDateTime != null)
                {
                    strSQL += " DeptCreateDate = '" + deptEntity.CreateDateTime + "',";
                    blstate = true;
                }
                if (deptEntity.ModifyUserID != -1)
                {
                    strSQL += " DeptModifyUserID = " + deptEntity.ModifyUserID;
                    blstate = true;
                }
                if (deptEntity.ModifyDateTime != "" && deptEntity.ModifyDateTime != null)
                {
                    strSQL += " DeptModifyDate = '" + deptEntity.ModifyDateTime + "',";
                    blstate = true;
                }
                if (deptEntity.RecordStatus != -1)
                {
                    strSQL += " DeptRecordStatus = " + deptEntity.RecordStatus +",";
                    blstate = true;
                }
                if (blstate)
                {
                    strSQL = strSQL.Substring(0, strSQL.Length - 1) + " where DeptID = @deptID";
                    SqlParameter[] values = new SqlParameter[]{
                        new SqlParameter("@deptID",deptID)
                    };
                    AppLog.Write("updateDeptSQL:" + strSQL, AppLog.LogMessageType.Debug);
                    //AppLog.Write(strSQL, AppLog.LogMessageType.Debug);
                    flag = DAL_DBGM.UpdateInfo(strSQL, values);
                    return flag;
                }
                else
                {
                    //没有需要更新的内容
                    return false;
                }
                
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---异常位置:LevelDataAccess.DAL_USER_Department.updateDeptByDeptID(decimal deptID, Entity_USER_Department deptEntity)---", AppLog.LogMessageType.Error, ex);
                throw ex;       
            }
        }

        /// <summary>
        /// 根据部门ID,删除该部门资料.
        /// </summary>
        /// <param name="deptID">待删除的部门ID</param>
        /// <returns>true:删除部门成功;false:删除部门失败.</returns>
        public bool deleteDeptByDeptID(decimal deptID)
        {
            try
            {
                string strSQL = "delete from [PER_DEPARTMENT] where DeptID = @deptID";
                SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@deptID",deptID)
                };
                return DAL_DBGM.DeleteInfo(strSQL, values);
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---异常位置:LevelDataAccess.DAL_USER_Department.deleteDeptByDeptID(decimal deptID)---", AppLog.LogMessageType.Error, ex);
                throw ex;
            }
        }
    }
}
